﻿Imports System.Data.SqlClient
Public Class UsuarioDAL
#Region "Incluir"
    Public Function Inserir(ByVal login As String, ByVal CodStatus As Integer, CodFunc As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try

            cmd.CommandText = "insert into Vendas.Gen_Usuario(usu_login,fun_codigo,sta_codigo,usu_dtcadastro) values (@descricao,@funcionario,@status,@data)"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = login
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = CodStatus
            cmd.Parameters.Add(New SqlParameter("@funcionario", SqlDbType.Int)).Value = CodFunc
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.DateTime)).Value = Format(Date.Now, "yyyy/MM/dd")
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Alterar"
    Public Function Alterar(ByVal Descricao As String, ByVal Status As Integer, ByVal Codigo As Integer, CodFunc As Integer, senha As Boolean) As Integer
        Dim cmd = New SqlCommand
        Try
            If (senha) Then
                cmd.CommandText = "update Vendas.Gen_Usuario set usu_login=@descricao, fun_codigo=@funcionario, sta_codigo=@status, usu_senha=NULL where usu_codigo=@codigo"
            Else
                cmd.CommandText = "update Vendas.Gen_Usuario set usu_login=@descricao, fun_codigo=@funcionario, sta_codigo=@status where usu_codigo=@codigo"
            End If
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = Status

            cmd.Parameters.Add(New SqlParameter("@funcionario", SqlDbType.Int)).Value = CodFunc
            Codigo = objconexao.executaCmd(cmd)

        Catch ex As Exception
            Throw ex

        Finally
            cmd = Nothing

        End Try
        Return Codigo
    End Function
#End Region

#Region "Excluir"
    Public Function Excluir(ByVal Codigo As Integer, Status As Integer) As Boolean

        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "update Vendas.Gen_Usuario set sta_codigo=@status where usu_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = Status

            Codigo = objconexao.executaCmd(cmd)

        Catch ex As Exception
            Throw ex

        Finally
            cmd = Nothing

        End Try
        Return Codigo
    End Function
#End Region

#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_VenUsuario where usu_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_VenUsuario where usu_login COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 2
                    cmd.CommandText = "select * from vw_VenUsuario where sta_descricao = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
                Case Else
                    cmd.CommandText = "select * from vw_VenUsuario order by usu_login"
            End Select

            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "Login"
    Public Function login(ByVal loginn As String, senhas As String) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_VenUsuario where usu_login COLLATE SQL_Latin1_General_CP1_CI_AI  = @descricao"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = loginn.Trim
            tab = objconexao.executaConsulta(cmd)

            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "Alterar"
    Public Function Alterarsenha(codigo As Integer, senha As String) As Integer
        Dim cmd = New SqlCommand
        Try

            cmd.CommandText = "update vendas.Gen_Usuario set usu_senha=@senha where usu_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@senha", SqlDbType.VarChar)).Value = senha
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            Codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing

        End Try
        Return Codigo
    End Function
#End Region
End Class
